package com.ifnk.portaljava.hlyt.dao.impl;

import com.ifnk.portaljava.dapWeb.advance.DBAccess;
import com.ifnk.portaljava.dapWeb.common.Encryption;
import com.ifnk.portaljava.hlyt.beans.Role;
import com.ifnk.portaljava.hlyt.beans.User;
import com.ifnk.portaljava.hlyt.beans.UserAllowedIp;
import com.ifnk.portaljava.hlyt.dao.IUserJdbcDao;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class UserJdbcDaoImpl implements IUserJdbcDao {

	public List findAll() throws Exception {
		DBAccess db = new DBAccess();
		ResultSet rs = null;
		String sql = "select c.id,c.name,c.password,c.enable,c.locked,c.createtime,c.disabletime,c.roleid,c.rolename,f.did as ipid,f.ip as ip from (select r.id as roleid,r.name as rolename,h.* from (select a.id as id,a.name as name,a.password as password,a.enable as enable,a.locked as locked,a.createtime as createtime,a.disabletime as disabletime,b.* from  user a  left outer join  user_role b on b.user_id = a.id and a.disabletime is null ) h left outer join role r on r.id=h.role_id) c,(select e.*,d.id as did,d.ip as ip from user e  left outer join user_allowed_ip d on d.user_id = e.id ) f where c.id = f.id  order by c.id asc;";
		System.out.println("sql:" + sql);
		rs = db.queryTable(sql);
		//int count = 0;
		int lastId = -1;
		int thisId = -1;
		User u = null;
		UserAllowedIp allowedIp;
		Role role;
		Iterator it;
		int ipId;
		int roleId;
		boolean ipFlag;
		boolean roleFlag;
		List<User> userList = new ArrayList<User>();
		try {
			while (rs.next()) {
				ipFlag = false;
				roleFlag = false;
				thisId = rs.getInt("id");
				allowedIp = new UserAllowedIp();
				allowedIp.setId(rs.getInt("ipid"));
				allowedIp.setIp(rs.getString("ip"));
				role = new Role();
				role.setId(rs.getInt("roleid"));
				role.setName(rs.getString("rolename"));
				if (thisId != lastId) {
					u = new User();
					u.setId(rs.getInt("id"));
					u.setName(rs.getString("name"));
					u.setPassword(rs.getString("password"));
					u.setEnable(rs.getInt("enable"));
					u.setLocked(rs.getInt("locked"));
					u.setCreatetime(rs.getTimestamp("createtime"));
					u.setDisabletime(rs.getTimestamp("disabletime"));
					u.setCreatetimeString(new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss").format(rs
							.getTimestamp("createtime")));
					if (rs.getTimestamp("disabletime") != null) {
						u.setDisabletimeString(new SimpleDateFormat(
								"yyyy-MM-dd HH:mm:ss").format(rs
								.getTimestamp("disabletime")));
					}
					userList.add(u);
				}
				it = u.getAllowedIpSet().iterator();
				while (it.hasNext()) {
					ipId = ((UserAllowedIp) it.next()).getId();
					if (ipId == allowedIp.getId()) { // 已经存在
						ipFlag = true;
						break;
					}
				}
				if (!ipFlag && allowedIp.getId() != 0) { // 如果不存在，则加入
					u.getAllowedIpSet().add(allowedIp);
				}

				it = u.getRoleSet().iterator();
				while (it.hasNext()) {
					roleId = ((Role) it.next()).getId();
					if (roleId == role.getId()) {
						roleFlag = true;
						break;
					}
				}
				if (!roleFlag && role.getId() != 0) {
					if (u.getRoleSet().size() == 0) {
						u.setRoleString(role.getName());
					} else {
						u.setRoleString(u.getRoleString() + ";"
								+ role.getName());
					}
					u.getRoleSet().add(role);
				}

				lastId = thisId;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("find All user error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return userList;
	}

	public int save(User user, Connection conn) throws Exception {
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int id = 0;
		try {
			String idSql = "select max(id) as maxId from user";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(idSql);
			while (rs.next()) {
				id = rs.getInt("maxId") + 1;
			}
			conn.setAutoCommit(false);
			String sql = "insert into user(id,name,password,enable,createtime,locked) values(?,?,?,?,now(),?)";
			System.out.println("insert sql:" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setString(2, user.getName());
			pstmt.setString(3, Encryption.encryptByMd5(user.getPassword()));
			pstmt.setInt(4, user.getEnable());
			pstmt.setInt(5, user.getLocked());
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("Save error!");
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;
	}

	public List findByName(String uName) throws Exception {
		DBAccess db = new DBAccess();
		ResultSet rs = null;
		List<User> userList = new ArrayList<User>();
		try {
			String sql = "select id,name,password from user where name='"
					+ uName + "' and disabletime is null;";
			System.out.println("sql:" + sql);
			rs = db.queryTable(sql);
			User u = null;
			while (rs.next()) {
				u = new User();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setPassword(rs.getString("password"));
				userList.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("find User By Name error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return userList;
	}

	public void delete(User user, Connection conn) throws Exception {
		PreparedStatement pstmt = null;
		try {
//			String sql = "update user set name = ?,password=?,enable = ?,locked=?,disabletime=now() where id=?";
			String sql = "delete from user where id="+user.getId();
			System.out.println("sql:" + sql);
			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, user.getName());
//			pstmt.setString(2, user.getPassword());
//			pstmt.setInt(3, user.getEnable());
//			pstmt.setInt(4, user.getLocked());
//			pstmt.setInt(5, user.getId());
//			pstmt.executeUpdate();
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("delete user error!");
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public void deleteUserRole(int userId, Connection conn) throws Exception {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "delete from user_role where user_id=" + userId;
			System.out.println("sql:" + sql);
			stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("deleteUserROle error!");
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void update(User user, Connection conn) throws Exception {
		PreparedStatement pstmt = null;
		try {
			String sql = "update user set name = ?,enable = ?,locked=?";
			System.out.println("password:"+user.getPassword()+":"+(user.getPassword() == null));
			if(user.getPassword() == null) {
				sql += " where id=?;";
			}else{
				sql += " ,password=? where id=?;";
			}
			System.out.println("sql:" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			pstmt.setInt(2, user.getEnable());
			pstmt.setInt(3, user.getLocked());
			if(user.getPassword() == null) {
				pstmt.setInt(4, user.getId());
			}else{
				pstmt.setString(4, Encryption.encryptByMd5(user.getPassword()));
				pstmt.setInt(5, user.getId());
			}
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("Update user error!");
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public void saveUserRole(Role role, int userId, Connection conn) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "insert into user_role(user_id,role_id) values(?,?)";
			System.out.println("insert sql:" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, userId);
			pstmt.setInt(2, role.getId());
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("save UserRole error!");
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public User validaty(String userName, String password) throws Exception {
		DBAccess db = new DBAccess();
		ResultSet rs = null;
		User user = null;
		try {
			String sql = "select id,name,password,enable,locked,laststattime from user where name='"
					+ userName + "' and password='" + Encryption.encryptByMd5(password) + "' and disabletime is null";
			System.out.println("sql:" + sql);
			rs = db.queryTable(sql);
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEnable(rs.getInt("enable"));
				user.setLocked(rs.getInt("locked"));
				user.setLaststattime(rs.getTimestamp("laststattime"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return user;
	}

	public void updateLocked(int userId, int locked, Connection conn) throws Exception {
		PreparedStatement pstmt = null;
		try {
			String sql = "update user set locked=?,laststattime=now() where id=?";
			System.out.println("sql:" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, locked);
			pstmt.setInt(2, userId);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("update user locked error!");
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) throws Exception {
		UserJdbcDaoImpl ujd = new UserJdbcDaoImpl();
		// ujd.validaty("aaa", "aaa");
		// List list = ujd.findAll();
		List list = ujd.findByRoleName("r1");
		
		int count = 0;
		for (int i = 0; i < list.size(); i++) {
			User u = (User) list.get(i);
			System.out.println(u.getId() + ":" + u.getName() + ":"
					+ u.getAllowedIpSet().size() + ":" + u.getRoleSet().size());
			Iterator it = u.getAllowedIpSet().iterator();
			while (it.hasNext()) {
				UserAllowedIp ip = (UserAllowedIp) it.next();
				System.out.println(ip.getId() + ":" + ip.getIp());
			}
			it = u.getRoleSet().iterator();
			while (it.hasNext()) {
				Role r = (Role) it.next();
				System.out.println(r.getId() + ":" + r.getName());
			}
			System.out.println("----------------------------");
			count++;
		}
		System.out.println(count);
	}

	public List<User> findByRoleName(String roleName) throws Exception {
		DBAccess db = new DBAccess();
		ResultSet rs = null;
		String sql = "";
		if(roleName == "") {
			sql = "select distinct id as uid from user where disabletime is null;";
		}else {
			sql = "select distinct ur.user_id as uid from user_role ur,(select r.id from role r where r.name like '%"
				+ roleName + "%') c where ur.role_id=c.id;";
		}
		System.out.println("sql:" + sql);
		rs = db.queryTable(sql);
		List<User> userList = new ArrayList<User>();
		// userId列表
		List<Integer> idList = new ArrayList<Integer>();
		int userId;
		User user;
		try {
			while (rs.next()) {
				idList.add(rs.getInt("uid"));
			}
			for (int i = 0; i < idList.size(); i++) {
				userId = idList.get(i);
				user = this.findById(userId);
				userList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("find User By Role Name error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return userList;
	}

	public User findById(int userId) throws Exception {
		DBAccess db = new DBAccess();
		ResultSet rs = null;
		String sql = " select c.*,d.* from (select a.*,r.id as roleid,r.name as rolename  from (select u.*,ur.* from user u,user_role ur where u.id="
				+ userId
				+ " and ur.user_id="
				+ userId
				+ " and u.disabletime is null) a,role r where r.id=a.role_id) c left outer join (select uip.id as ipid,uip.ip as ip,uip.user_id as user_id from user_allowed_ip uip where uip.user_id="
				+ userId + ") d on c.id = d.user_id;";

		System.out.println("sql:" + sql);
		rs = db.queryTable(sql);
		User user = null;
		Role role = null;
		UserAllowedIp ip = null;
		int count = 0;
		int roleId;
		int ipId;
		boolean ipFlag;
		boolean roleFlag;
		Iterator it;
		try {
			while (rs.next()) {
				ipFlag = false;
				roleFlag = false;
				if (count == 0) {
					user = new User();
					user = new User();
					user.setId(rs.getInt("id"));
					user.setName(rs.getString("name"));
					user.setPassword(rs.getString("password"));
					user.setEnable(rs.getInt("enable"));
					user.setLocked(rs.getInt("locked"));
					user.setCreatetime(rs.getTimestamp("createtime"));
					user.setDisabletime(rs.getTimestamp("disabletime"));
					user.setLaststattime(rs.getTimestamp("laststattime"));
					user.setCreatetimeString(new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss").format(rs
							.getTimestamp("createtime")));
					if (rs.getTimestamp("disabletime") != null) {
						user.setDisabletimeString(new SimpleDateFormat(
								"yyyy-MM-dd HH:mm:ss").format(rs
								.getTimestamp("disabletime")));
					}
				}
				
				roleId = rs.getInt("roleid");
				ipId = rs.getInt("ipid");
				it = user.getRoleSet().iterator();
				while (it.hasNext()) {
					if (roleId == ((Role) it.next()).getId()) {
						roleFlag = true;
						break;
					}
				}

				it = user.getAllowedIpSet().iterator();
				while (it.hasNext()) {
					if (ipId == ((UserAllowedIp) it.next()).getId()) {
						ipFlag = true;
						break;
					}
				}
				
				if(!roleFlag) {
					role = new Role();
					role.setId(roleId);
					role.setName(rs.getString("rolename"));
					if(count == 0){
						user.setRoleString(role.getName());
					}else{
						user.setRoleString(user.getRoleString()+";"+role.getName());
					}
					user.getRoleSet().add(role);
				}
				
				if(!ipFlag && ipId != 0) {
					ip = new UserAllowedIp();
					ip.setId(ipId);
					ip.setIp(rs.getString("ip"));
					user.getAllowedIpSet().add(ip);
				}
				
				count ++;

			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("find User By Id error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return user;
	}
}
